package com.cencat.merchant.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cencat.merchant.entity.Delivery;
import com.cencat.merchant.dto.DeliveryQueryDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 配送管理Mapper接口
 * 提供配送数据的增删改查、统计分析、智能匹配等数据访问功能
 * 
 * @author cencat
 * @version 1.0
 * @since 2025-01-20
 */
@Mapper
public interface DeliveryMapper extends BaseMapper<Delivery> {

    /**
     * 分页查询配送列表
     * 
     * @param page 分页参数
     * @param queryDTO 查询条件
     * @return 配送分页数据
     */
    Page<Delivery> selectDeliveryPage(Page<Delivery> page, @Param("query") DeliveryQueryDTO queryDTO);

    /**
     * 批量更新配送状态
     * 
     * @param ids 配送ID列表
     * @param status 新状态
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE delivery SET status = #{status}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateStatus(@Param("ids") List<Long> ids, @Param("status") String status);

    /**
     * 批量更新配送优先级
     * 
     * @param ids 配送ID列表
     * @param priority 优先级
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE delivery SET priority = #{priority}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdatePriority(@Param("ids") List<Long> ids, @Param("priority") Integer priority);

    /**
     * 批量分配配送员
     * 
     * @param ids 配送ID列表
     * @param deliveryManId 配送员ID
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE delivery SET delivery_man_id = #{deliveryManId}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchAssignDeliveryMan(@Param("ids") List<Long> ids, @Param("deliveryManId") Long deliveryManId);

    /**
     * 批量更新配送费用
     * 
     * @param ids 配送ID列表
     * @param deliveryFee 配送费用
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE delivery SET delivery_fee = #{deliveryFee}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateDeliveryFee(@Param("ids") List<Long> ids, @Param("deliveryFee") BigDecimal deliveryFee);

    /**
     * 根据状态统计配送数量
     * 
     * @param status 配送状态
     * @return 配送数量
     */
    @Select("SELECT COUNT(*) FROM delivery WHERE status = #{status}")
    Long countByStatus(@Param("status") String status);

    /**
     * 根据配送类型统计配送数量
     * 
     * @param deliveryType 配送类型
     * @return 配送数量
     */
    @Select("SELECT COUNT(*) FROM delivery WHERE delivery_type = #{deliveryType}")
    Long countByDeliveryType(@Param("deliveryType") String deliveryType);

    /**
     * 计算平均配送时长
     * 
     * @return 平均时长（分钟）
     */
    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) FROM delivery WHERE status = 'COMPLETED'")
    BigDecimal calculateAverageDuration();

    /**
     * 计算平均配送费用
     * 
     * @return 平均费用
     */
    @Select("SELECT AVG(delivery_fee) FROM delivery WHERE status = 'COMPLETED'")
    BigDecimal calculateAverageDeliveryFee();

    /**
     * 查询待配送列表
     * 
     * @param limit 限制数量
     * @return 待配送列表
     */
    @Select("SELECT * FROM delivery WHERE status = 'PENDING' ORDER BY priority DESC, create_time ASC LIMIT #{limit}")
    List<Delivery> selectPendingDeliveries(@Param("limit") Integer limit);

    /**
     * 查询配送中列表
     * 
     * @param limit 限制数量
     * @return 配送中列表
     */
    @Select("SELECT * FROM delivery WHERE status = 'IN_TRANSIT' ORDER BY update_time DESC LIMIT #{limit}")
    List<Delivery> selectInTransitDeliveries(@Param("limit") Integer limit);

    /**
     * 查询已完成配送列表
     * 
     * @param limit 限制数量
     * @return 已完成配送列表
     */
    @Select("SELECT * FROM delivery WHERE status = 'COMPLETED' ORDER BY end_time DESC LIMIT #{limit}")
    List<Delivery> selectCompletedDeliveries(@Param("limit") Integer limit);

    /**
     * 查询紧急配送列表
     * 
     * @param limit 限制数量
     * @return 紧急配送列表
     */
    @Select("SELECT * FROM delivery WHERE priority >= 8 AND status IN ('PENDING', 'IN_TRANSIT') ORDER BY priority DESC, create_time ASC LIMIT #{limit}")
    List<Delivery> selectUrgentDeliveries(@Param("limit") Integer limit);

    /**
     * 根据配送类型查询配送列表
     * 
     * @param deliveryType 配送类型
     * @param status 配送状态
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE delivery_type = #{deliveryType} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY update_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectDeliveriesByType(@Param("deliveryType") String deliveryType, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据配送员ID查询配送列表
     * 
     * @param deliveryManId 配送员ID
     * @param status 配送状态
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE delivery_man_id = #{deliveryManId} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY update_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectDeliveriesByDeliveryMan(@Param("deliveryManId") Long deliveryManId, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据收货地址查询配送列表
     * 
     * @param receiverAddress 收货地址
     * @param status 配送状态
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE receiver_address LIKE CONCAT('%', #{receiverAddress}, '%') " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY update_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectDeliveriesByAddress(@Param("receiverAddress") String receiverAddress, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据时间范围查询配送列表
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 配送状态
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE start_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY start_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectDeliveriesByTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 智能匹配配送
     * 
     * @param deliveryType 配送类型
     * @param deliveryManId 配送员ID
     * @param priority 最小优先级
     * @param maxDistance 最大距离（公里）
     * @param maxDeliveryFee 最大配送费用
     * @param limit 限制数量
     * @return 匹配的配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery " +
            "WHERE status = 'PENDING' " +
            "<if test='deliveryType != null and deliveryType != \"\">" +
            "AND delivery_type = #{deliveryType} " +
            "</if>" +
            "<if test='deliveryManId != null'>" +
            "AND delivery_man_id = #{deliveryManId} " +
            "</if>" +
            "<if test='priority != null'>" +
            "AND priority >= #{priority} " +
            "</if>" +
            "<if test='maxDistance != null'>" +
            "AND distance <= #{maxDistance} " +
            "</if>" +
            "<if test='maxDeliveryFee != null'>" +
            "AND delivery_fee <= #{maxDeliveryFee} " +
            "</if>" +
            "ORDER BY priority DESC, create_time ASC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectMatchedDeliveries(@Param("deliveryType") String deliveryType, @Param("deliveryManId") Long deliveryManId, @Param("priority") Integer priority, @Param("maxDistance") BigDecimal maxDistance, @Param("maxDeliveryFee") BigDecimal maxDeliveryFee, @Param("limit") Integer limit);

    /**
     * 检查配送编码是否存在
     * 
     * @param deliveryCode 配送编码
     * @param excludeId 排除的配送ID
     * @return 存在数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM delivery WHERE delivery_code = #{deliveryCode} " +
            "<if test='excludeId != null'>" +
            "AND id != #{excludeId}" +
            "</if>" +
            "</script>")
    int existsByDeliveryCode(@Param("deliveryCode") String deliveryCode, @Param("excludeId") Long excludeId);

    /**
     * 搜索配送
     * 
     * @param keyword 关键词
     * @param searchType 搜索类型
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE 1=1 " +
            "<choose>" +
            "<when test='searchType == \"code\"'>" +
            "AND delivery_code LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<when test='searchType == \"receiver\"'>" +
            "AND receiver_name LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<when test='searchType == \"address\"'>" +
            "AND receiver_address LIKE CONCAT('%', #{keyword}, '%') " +
            "</when>" +
            "<otherwise>" +
            "AND (delivery_code LIKE CONCAT('%', #{keyword}, '%') " +
            "OR receiver_name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR receiver_address LIKE CONCAT('%', #{keyword}, '%')) " +
            "</otherwise>" +
            "</choose>" +
            "ORDER BY update_time DESC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> searchDeliveries(@Param("keyword") String keyword, @Param("searchType") String searchType, @Param("limit") Integer limit);

    /**
     * 查询配送列表（用于导出）
     * 
     * @param queryDTO 查询条件
     * @return 配送列表
     */
    List<Delivery> selectDeliveryList(@Param("query") DeliveryQueryDTO queryDTO);

    /**
     * 计算指定时间范围内的配送数量
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 配送状态
     * @return 配送数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM delivery " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status}" +
            "</if>" +
            "</script>")
    Integer calculateDeliveryCount(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status);

    /**
     * 计算指定时间范围内的平均配送时长
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 平均时长（分钟）
     */
    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) FROM delivery " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} AND status = 'COMPLETED'")
    Double calculateAverageDurationByTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 计算指定时间范围内的总配送费用
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param status 配送状态
     * @return 总配送费用
     */
    @Select("<script>" +
            "SELECT COALESCE(SUM(delivery_fee), 0) FROM delivery " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status}" +
            "</if>" +
            "</script>")
    BigDecimal calculateTotalDeliveryFee(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("status") String status);

    /**
     * 获取配送趋势数据
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param trendType 趋势类型
     * @return 趋势数据
     */
    List<Map<String, Object>> selectDeliveryTrends(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("trendType") String trendType);

    /**
     * 获取配送排行榜
     * 
     * @param rankType 排行类型
     * @param limit 限制数量
     * @return 排行榜数据
     */
    List<Map<String, Object>> selectDeliveryRanking(@Param("rankType") String rankType, @Param("limit") Integer limit);

    /**
     * 获取配送分布数据
     * 
     * @param distributionType 分布类型
     * @return 分布数据
     */
    List<Map<String, Object>> selectDeliveryDistribution(@Param("distributionType") String distributionType);

    /**
     * 更新配送开始时间
     * 
     * @param deliveryId 配送ID
     * @param startTime 开始时间
     * @return 更新记录数
     */
    @Update("UPDATE delivery SET start_time = #{startTime}, status = 'IN_TRANSIT', update_time = NOW() WHERE id = #{deliveryId}")
    int updateStartTime(@Param("deliveryId") Long deliveryId, @Param("startTime") LocalDateTime startTime);

    /**
     * 更新配送结束时间
     * 
     * @param deliveryId 配送ID
     * @param endTime 结束时间
     * @return 更新记录数
     */
    @Update("UPDATE delivery SET end_time = #{endTime}, status = 'COMPLETED', update_time = NOW() WHERE id = #{deliveryId}")
    int updateEndTime(@Param("deliveryId") Long deliveryId, @Param("endTime") LocalDateTime endTime);

    /**
     * 更新配送进度
     * 
     * @param deliveryId 配送ID
     * @param progress 进度百分比
     * @return 更新记录数
     */
    @Update("UPDATE delivery SET progress = #{progress}, update_time = NOW() WHERE id = #{deliveryId}")
    int updateProgress(@Param("deliveryId") Long deliveryId, @Param("progress") Integer progress);

    /**
     * 更新配送位置
     * 
     * @param deliveryId 配送ID
     * @param currentLatitude 当前纬度
     * @param currentLongitude 当前经度
     * @return 更新记录数
     */
    @Update("UPDATE delivery SET current_latitude = #{currentLatitude}, current_longitude = #{currentLongitude}, update_time = NOW() WHERE id = #{deliveryId}")
    int updateLocation(@Param("deliveryId") Long deliveryId, @Param("currentLatitude") BigDecimal currentLatitude, @Param("currentLongitude") BigDecimal currentLongitude);

    /**
     * 批量更新配送进度
     * 
     * @param deliveryIds 配送ID列表
     * @param progress 进度百分比
     * @return 更新记录数
     */
    @Update("<script>" +
            "UPDATE delivery SET progress = #{progress}, update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='deliveryIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateProgress(@Param("deliveryIds") List<Long> deliveryIds, @Param("progress") Integer progress);

    /**
     * 获取配送统计数据
     * 
     * @param deliveryId 配送ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计数据
     */
    Map<String, Object> selectDeliveryStats(@Param("deliveryId") Long deliveryId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取配送效率统计
     * 
     * @param deliveryId 配送ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 效率统计数据
     */
    Map<String, Object> selectDeliveryEfficiencyStats(@Param("deliveryId") Long deliveryId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取配送成本统计
     * 
     * @param deliveryId 配送ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 成本统计数据
     */
    Map<String, Object> selectDeliveryCostStats(@Param("deliveryId") Long deliveryId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取配送员配送统计
     * 
     * @param deliveryManId 配送员ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 配送员配送统计数据
     */
    Map<String, Object> selectDeliveryManStats(@Param("deliveryManId") Long deliveryManId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取区域配送统计
     * 
     * @param region 区域
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 区域配送统计数据
     */
    Map<String, Object> selectRegionDeliveryStats(@Param("region") String region, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 获取配送类型统计
     * 
     * @param deliveryType 配送类型
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 配送类型统计数据
     */
    Map<String, Object> selectDeliveryTypeStats(@Param("deliveryType") String deliveryType, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 根据距离范围查询配送列表
     * 
     * @param minDistance 最小距离
     * @param maxDistance 最大距离
     * @param status 配送状态
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE distance BETWEEN #{minDistance} AND #{maxDistance} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY distance ASC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectDeliveriesByDistance(@Param("minDistance") BigDecimal minDistance, @Param("maxDistance") BigDecimal maxDistance, @Param("status") String status, @Param("limit") Integer limit);

    /**
     * 根据费用范围查询配送列表
     * 
     * @param minFee 最小费用
     * @param maxFee 最大费用
     * @param status 配送状态
     * @param limit 限制数量
     * @return 配送列表
     */
    @Select("<script>" +
            "SELECT * FROM delivery WHERE delivery_fee BETWEEN #{minFee} AND #{maxFee} " +
            "<if test='status != null and status != \"\">" +
            "AND status = #{status} " +
            "</if>" +
            "ORDER BY delivery_fee ASC " +
            "<if test='limit != null'>" +
            "LIMIT #{limit}" +
            "</if>" +
            "</script>")
    List<Delivery> selectDeliveriesByFeeRange(@Param("minFee") BigDecimal minFee, @Param("maxFee") BigDecimal maxFee, @Param("status") String status, @Param("limit") Integer limit);
}